Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.IO
Imports System.Data

Partial Class AdobePopup2
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Dim sDT As New Data.DataTable

        If IsNothing(Request.QueryString("sqlReport")) Then Exit Sub

        Dim pFunc As New PolyFunc.Secure
        Dim strSeqCd As String = pFunc.DecodeBase64(Request.QueryString("sqlReport"))
        pFunc = Nothing

        ' *** mpz_Main ***
        Dim sConn As New Data.SqlClient.SqlConnection
        Dim sCom As New Data.SqlClient.SqlCommand
        Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)
        Dim sDS As New Data.DataSet
        sCom.CommandType = Data.CommandType.Text
        sCom.CommandText = "SELECT policy_trans.policy_no, policy_client_dtl.policy_client_last_name + " & _
          "', ' + policy_client_dtl.policy_client_first_name + " & _
          "' ' + policy_client_dtl.policy_client_middle_name AS Name, broker_mstr.broker_desc, " & _
          "policy_client_dtl.policy_client_address + ' ' + policy_client_dtl.policy_client_address2 + " & _
          "' ' + country_city_dtl.city_desc + ' ' + country_mstr.country_desc AS Address, " & _
          "provider_mstr.provider_desc + ' - ' + prdt_mstr.prdt_desc as prdt_desc, policy_client_dtl.tel_no, policy_client_dtl.mobile_no, policy_trans.policy_seq_cd " & _
          "FROM prdt_mstr INNER JOIN broker_mstr INNER JOIN provider_mstr ON broker_mstr.broker_cd = " & _
          "provider_mstr.broker_cd ON prdt_mstr.provider_cd = provider_mstr.provider_cd INNER JOIN " & _
          "policy_client_dtl INNER JOIN policy_trans ON policy_client_dtl.policy_seq_cd = " & _
          "policy_trans.policy_seq_cd INNER JOIN country_city_dtl ON policy_client_dtl.city_cd = " & _
          "country_city_dtl.city_cd INNER JOIN country_mstr ON country_city_dtl.country_cd = " & _
          "country_mstr.country_cd ON prdt_mstr.prdt_cd = policy_trans.prdt_cd " & _
          "WHERE (policy_client_dtl.owner_tag = '1') AND (policy_trans.policy_seq_cd='" & strSeqCd & "')"
        ' *** mpz_Main ***

        ' *** mpz_Section2 ***
        Dim sCom2 As New Data.SqlClient.SqlCommand
        Dim sDApt2 As New Data.SqlClient.SqlDataAdapter(sCom2)
        Dim sDS2 As New Data.DataSet
        sCom2.CommandType = Data.CommandType.Text
        sCom2.CommandText = "SELECT dbo.fn_GetBirthDate(policy_approval_dt) AS Approval_Date, establish_period, commence_age, " & _
          "insured_amt AS Sum_Insured, contribution_amt AS Min_Contr_Period FROM policy_trans " & _
          "WHERE (policy_seq_cd = '" & strSeqCd & "')"
        ' *** mpz_Section2 ***

        ' *** mpz_Benefs ***
        Dim sCom3 As New Data.SqlClient.SqlCommand
        Dim sDApt3 As New Data.SqlClient.SqlDataAdapter(sCom3)
        Dim sDS3 As New Data.DataSet
        sCom3.CommandType = Data.CommandType.Text
        sCom3.CommandText = "SELECT policy_bene_dtl.policy_bene_last_name, policy_bene_dtl.policy_bene_first_name, " & _
          "policy_bene_dtl.policy_bene_middle_name, dbo.fn_GetBirthDate(policy_bene_dtl.birthday) as birthday, lookup_mstr.Lookup_desc AS Gender, " & _
          "lookup_mstr_1.Lookup_desc AS Relationship, policy_bene_dtl.share FROM policy_bene_dtl INNER JOIN " & _
          "lookup_mstr ON policy_bene_dtl.gender = lookup_mstr.Lookup_seq INNER JOIN lookup_mstr AS " & _
          "lookup_mstr_1 ON policy_bene_dtl.relationship = lookup_mstr_1.Lookup_seq INNER JOIN policy_trans ON " & _
          "policy_bene_dtl.policy_seq_cd = policy_trans.policy_seq_cd WHERE (lookup_mstr.Lookup_cd = 'GENDER') " & _
          "AND (lookup_mstr_1.Lookup_cd = 'RELEATIONSHIP') AND (policy_trans.policy_seq_cd = '" & strSeqCd & "')"
        ' *** mpz_Benefs ***

        ' *** mpz_Attachments ***
        Dim sCom4 As New Data.SqlClient.SqlCommand
        Dim sDApt4 As New Data.SqlClient.SqlDataAdapter(sCom4)
        'Dim sDS4 As New Data.DataSet
        'sCom4.CommandType = Data.CommandType.Text
        'sCom4.CommandText = "SELECT policy_bene_attach.caption, policy_bene_attach.filename " & _
        '  "FROM policy_bene_attach INNER JOIN policy_trans ON policy_bene_attach.policy_seq_cd = " & _
        '  "policy_trans.policy_seq_cd WHERE (policy_trans.policy_no = '" & Session("sqlReport") & "') " & _
        '  "UNION " & _
        '  "SELECT policy_client_attach.caption, policy_client_attach.filename " & _
        '  "FROM policy_client_attach INNER JOIN policy_trans ON policy_client_attach.policy_seq_cd = " & _
        '  "policy_trans.policy_seq_cd WHERE (policy_trans.policy_no = '" & Session("sqlReport") & "') " & _
        '  "UNION " & _
        '  "SELECT policy_insured_attach.caption, policy_insured_attach.filename " & _
        '  "FROM policy_insured_attach INNER JOIN policy_trans ON policy_insured_attach.policy_seq_cd = " & _
        '  "policy_trans.policy_seq_cd WHERE (policy_trans.policy_no = '" & Session("sqlReport") & "') " & _
        '  "UNION " & _
        '  "SELECT policy_trustees_attach.caption, policy_trustees_attach.filename " & _
        '  "FROM policy_trustees_attach INNER JOIN policy_trans ON policy_trustees_attach.policy_seq_cd = " & _
        '  "policy_trans.policy_seq_cd WHERE (policy_trans.policy_no ='" & Session("sqlReport") & "')"
        ' *** mpz_Attachments ***

        'Try
        sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
        sConn.Open()

        Dim objReport As New ReportDocument
        'objReport.Load(Server.MapPath(Session("sqlUseReport").ToString) & ".rpt")
        objReport.Load(Server.MapPath("~/Management_Info/PolicyAllDetails.rpt"))

        Dim sComSec1 As New Data.SqlClient.SqlCommand
        Dim sDAptSec1 As New Data.SqlClient.SqlDataAdapter(sComSec1)
        Dim sDSSec1 As New Data.DataSet

        sComSec1.CommandText = "Select * from vw_rpt_policy_sec1 WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec1.Connection = sConn
        sDAptSec1.Fill(sDSSec1)
        sDSSec1.Tables(0).TableName = "vw_rpt_policy_sec1"
        objReport.Subreports("Policy_Section1.rpt").SetDataSource(sDSSec1)

        Dim sComSec3 As New Data.SqlClient.SqlCommand
        Dim sDAptSec3 As New Data.SqlClient.SqlDataAdapter(sComSec3)
        Dim sDSSec3 As New Data.DataSet

        sComSec3.CommandText = "Select * from vw_rpt_policy_sec3 WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec3.Connection = sConn
        sDAptSec3.Fill(sDSSec3)
        sDSSec3.Tables(0).TableName = "vw_rpt_policy_sec3"
        objReport.Subreports("Policy_Section3.rpt").SetDataSource(sDSSec3)

        Dim sComSec4 As New Data.SqlClient.SqlCommand
        Dim sDAptSec4 As New Data.SqlClient.SqlDataAdapter(sComSec4)
        Dim sDSSec4 As New Data.DataSet

        sComSec4.CommandText = "Select * from vw_rpt_policy_sec4 WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec4.Connection = sConn
        sDAptSec4.Fill(sDSSec4)
        sDSSec4.Tables(0).TableName = "vw_rpt_policy_sec4"
        objReport.Subreports("Policy_Section4.rpt").SetDataSource(sDSSec4)

        Dim sComSec5 As New Data.SqlClient.SqlCommand
        Dim sDAptSec5 As New Data.SqlClient.SqlDataAdapter(sComSec5)
        Dim sDSSec5 As New Data.DataSet

        sComSec5.CommandText = "Select * from vw_rpt_policy_sec5 WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec5.Connection = sConn
        sDAptSec5.Fill(sDSSec5)
        sDSSec5.Tables(0).TableName = "vw_rpt_policy_sec5"
        objReport.Subreports("Policy_Section5.rpt").SetDataSource(sDSSec5)

        Dim sComSec_client As New Data.SqlClient.SqlCommand
        Dim sDAptSec_client As New Data.SqlClient.SqlDataAdapter(sComSec_client)
        Dim sDSSec_client As New Data.DataSet

        sComSec_client.CommandText = "Select * from vw_rpt_policy_client_dtl WHERE policy_seq_cd ='" & strSeqCd & "' And owner_tag = 1"
        sComSec_client.Connection = sConn
        sDAptSec_client.Fill(sDSSec_client)
        sDSSec_client.Tables(0).TableName = "vw_rpt_policy_client_dtl"
        objReport.Subreports("Policy_Owner_Info.rpt").SetDataSource(sDSSec_client)

        Dim sComSec_insured As New Data.SqlClient.SqlCommand
        Dim sDAptSec_insured As New Data.SqlClient.SqlDataAdapter(sComSec_insured)
        Dim sDSSec_insured As New Data.DataSet

        sComSec_insured.CommandText = "Select * from vw_rpt_policy_client_dtl WHERE policy_seq_cd ='" & strSeqCd & "' And insured_tag = 1"
        sComSec_insured.Connection = sConn
        sDAptSec_insured.Fill(sDSSec_insured)
        sDSSec_insured.Tables(0).TableName = "vw_rpt_policy_client_dtl"
        objReport.Subreports("Policy_Insured_Info.rpt").SetDataSource(sDSSec_insured)

        Dim sComSec_prem As New Data.SqlClient.SqlCommand
        Dim sDAptSec_prem As New Data.SqlClient.SqlDataAdapter(sComSec_prem)
        Dim sDSSec_prem As New Data.DataSet

        sComSec_prem.CommandText = "Select * from vw_rpt_prem_rider WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec_prem.Connection = sConn
        sDAptSec_prem.Fill(sDSSec_prem)
        sDSSec_prem.Tables(0).TableName = "vw_rpt_prem_rider"
        objReport.Subreports("Policy_Prem_Rider.rpt").SetDataSource(sDSSec_prem)

        Dim sComSec_fund As New Data.SqlClient.SqlCommand
        Dim sDAptSec_fund As New Data.SqlClient.SqlDataAdapter(sComSec_fund)
        Dim sDSSec_fund As New Data.DataSet

        sComSec_fund.CommandText = "Select * from vw_rpt_policy_fund WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec_fund.Connection = sConn
        sDAptSec_fund.Fill(sDSSec_fund)
        sDSSec_fund.Tables(0).TableName = "vw_rpt_policy_fund"
        objReport.Subreports("Policy_Investments.rpt").SetDataSource(sDSSec_fund)

        Dim sComSec_trust As New Data.SqlClient.SqlCommand
        Dim sDAptSec_trust As New Data.SqlClient.SqlDataAdapter(sComSec_trust)
        Dim sDSSec_trust As New Data.DataSet

        sComSec_trust.CommandText = "Select * from vw_rpt_policy_trustee WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec_trust.Connection = sConn
        sDAptSec_trust.Fill(sDSSec_trust)
        sDSSec_trust.Tables(0).TableName = "vw_rpt_policy_trustee"
        objReport.Subreports("Policy_Trustees.rpt").SetDataSource(sDSSec_trust)

        Dim sComSec_attach As New Data.SqlClient.SqlCommand
        Dim sDAptSec_attach As New Data.SqlClient.SqlDataAdapter(sComSec_attach)
        Dim sDSSec_attach As New Data.DataSet

        sComSec_attach.CommandText = "sp_get_policy_attachments"
        sComSec_attach.CommandType = CommandType.StoredProcedure
        sComSec_attach.Parameters.AddWithValue("@policy_no", strSeqCd)

        sComSec_attach.Connection = sConn
        sDAptSec_attach.Fill(sDSSec_attach)
        sDSSec_attach.Tables(0).TableName = "mpz_Attachments"
        objReport.Subreports("Policy_Attachments.rpt").SetDataSource(sDSSec_attach)

        Dim sComSec_pay As New Data.SqlClient.SqlCommand
        Dim sDAptSec_pay As New Data.SqlClient.SqlDataAdapter(sComSec_pay)
        Dim sDSSec_pay As New Data.DataSet

        sComSec_pay.CommandText = "Select * from vw_rpt_policy_payment WHERE policy_seq_cd ='" & strSeqCd & "'"
        sComSec_pay.Connection = sConn
        sDAptSec_pay.Fill(sDSSec_pay)
        sDSSec_pay.Tables(0).TableName = "vw_rpt_policy_payment"
        objReport.Subreports("Policy_Payment.rpt").SetDataSource(sDSSec_pay)

        sCom.Connection = sConn
        sCom2.Connection = sConn
        sCom3.Connection = sConn
        sCom4.Connection = sConn

        sDApt.Fill(sDS)
        sDApt2.Fill(sDS2)
        sDApt3.Fill(sDS3)
        'sDApt4.Fill(sDS4)

        sDS.Tables(0).TableName = "mpz_Main"
        sDS2.Tables(0).TableName = "mpz_Section2"
        sDS3.Tables(0).TableName = "mpz_Benefs"
        'sDS4.Tables(0).TableName = "mpz_Attachments"


        objReport.SetDataSource(sDS)
        objReport.Subreports("Policy_Section2.rpt").SetDataSource(sDS2)
        objReport.Subreports("Policy_Benefs.rpt").SetDataSource(sDS3)
        'objReport.Subreports("Policy_Attachments.rpt").SetDataSource(sDS4)

        Dim expReport As ExportOptions
        expReport = objReport.ExportOptions
        expReport.FormatOptions = New PdfRtfWordFormatOptions 'PDF format option
        expReport.ExportFormatType = ExportFormatType.PortableDocFormat 'Set the format type of document
        Dim req As ExportRequestContext = New ExportRequestContext
        req.ExportInfo = expReport

        'Sets the report to HTML stream
        Dim st As System.IO.Stream
        st = objReport.FormatEngine.ExportToStream(req)

        objReport = Nothing 'Dispose the report

        'Stream content
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"

        Dim b(st.Length) As Byte
        st.Read(b, 0, st.Length) 'Read Stream
        Response.BinaryWrite(b) 'Write Stream
        st.Close() 'Close Stream
        st = Nothing 'Dispose

        Response.End()

        'Catch ex As Exception
        '    System.Diagnostics.Debug.Print(ex.Message)
        'Finally
        sConn.Close()
        'End Try

    End Sub
End Class

'mpz_main
'SELECT     dbo.policy_trans.policy_no, 
'                      dbo.policy_client_dtl.policy_client_last_name + ', ' + dbo.policy_client_dtl.policy_client_first_name + ' ' + dbo.policy_client_dtl.policy_client_middle_name
'                       AS Name, dbo.broker_mstr.broker_desc, 
'                      dbo.policy_client_dtl.policy_client_address + ' ' + dbo.policy_client_dtl.policy_client_address2 + ' ' + dbo.country_city_dtl.city_desc + ' ' + dbo.country_mstr.country_desc
'                       AS Address, dbo.prdt_mstr.prdt_desc, dbo.policy_client_dtl.tel_no, dbo.policy_client_dtl.mobile_no, dbo.policy_trans.policy_seq_cd
'FROM         dbo.prdt_mstr INNER JOIN
'                      dbo.broker_mstr INNER JOIN
'                      dbo.provider_mstr ON dbo.broker_mstr.broker_cd = dbo.provider_mstr.broker_cd ON 
'                      dbo.prdt_mstr.provider_cd = dbo.provider_mstr.provider_cd INNER JOIN
'                      dbo.policy_client_dtl INNER JOIN
'                      dbo.policy_trans ON dbo.policy_client_dtl.policy_seq_cd = dbo.policy_trans.policy_seq_cd INNER JOIN
'                      dbo.country_city_dtl ON dbo.policy_client_dtl.city_cd = dbo.country_city_dtl.city_cd INNER JOIN
'                      dbo.country_mstr ON dbo.country_city_dtl.country_cd = dbo.country_mstr.country_cd ON dbo.prdt_mstr.prdt_cd = dbo.policy_trans.prdt_cd
'WHERE     (dbo.policy_client_dtl.owner_tag = '1')

'mpz_Attachments
'SELECT     dbo.policy_bene_attach.caption, dbo.policy_bene_attach.filename
'FROM         dbo.policy_bene_attach INNER JOIN
'                      dbo.policy_trans ON dbo.policy_bene_attach.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.policy_trans.policy_no = N'1')
'UNION
'SELECT     dbo.policy_client_attach.caption, dbo.policy_client_attach.filename
'FROM         dbo.policy_client_attach INNER JOIN
'                      dbo.policy_trans ON dbo.policy_client_attach.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.policy_trans.policy_no = N'1')
'UNION
'SELECT     dbo.policy_insured_attach.caption, dbo.policy_insured_attach.filename
'FROM         dbo.policy_insured_attach INNER JOIN
'                      dbo.policy_trans ON dbo.policy_insured_attach.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.policy_trans.policy_no = N'1')
'UNION
'SELECT     dbo.policy_trustees_attach.caption, dbo.policy_trustees_attach.filename
'FROM         dbo.policy_trustees_attach INNER JOIN
'                      dbo.policy_trans ON dbo.policy_trustees_attach.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.policy_trans.policy_no = N'1')

'mpz_Benefs
'SELECT     dbo.policy_bene_dtl.policy_bene_last_name, dbo.policy_bene_dtl.policy_bene_first_name, dbo.policy_bene_dtl.policy_bene_middle_name, 
'                      dbo.policy_bene_dtl.birthday, dbo.lookup_mstr.Lookup_desc AS Gender, lookup_mstr_1.Lookup_desc AS Relationship, 
'                      dbo.policy_bene_dtl.share
'FROM         dbo.policy_bene_dtl INNER JOIN
'                      dbo.lookup_mstr ON dbo.policy_bene_dtl.gender = dbo.lookup_mstr.Lookup_seq INNER JOIN
'                      dbo.lookup_mstr AS lookup_mstr_1 ON dbo.policy_bene_dtl.relationship = lookup_mstr_1.Lookup_seq INNER JOIN
'                      dbo.policy_trans ON dbo.policy_bene_dtl.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.lookup_mstr.Lookup_cd = N'GENDER') AND (lookup_mstr_1.Lookup_cd = N'RELEATIONSHIP') AND (dbo.policy_trans.policy_no = N'1')

'mpz_Insured_Info
'SELECT     dbo.policy_insured_dtl.policy_insured_last_name, dbo.policy_insured_dtl.policy_insured_first_name, 
'                      dbo.policy_insured_dtl.policy_insured_middle_name, dbo.policy_insured_dtl.policy_insured_alias, dbo.policy_insured_dtl.birthday, 
'                      dbo.policy_insured_dtl.id_no, dbo.policy_insured_dtl.occupation, dbo.policy_insured_dtl.policy_insured_position, 
'                      dbo.policy_insured_dtl.nationality_cd, dbo.lookup_mstr.Lookup_desc AS Salutation, 
'                      lookup_mstr_1.Lookup_desc AS Marital_Status, lookup_mstr_2.Lookup_desc AS Gender
'FROM         dbo.lookup_mstr INNER JOIN
'                      dbo.policy_insured_dtl ON dbo.lookup_mstr.Lookup_seq = dbo.policy_insured_dtl.policy_insured_salutation INNER JOIN
'                      dbo.lookup_mstr AS lookup_mstr_1 ON dbo.policy_insured_dtl.marital_st = lookup_mstr_1.Lookup_seq INNER JOIN
'                      dbo.lookup_mstr AS lookup_mstr_2 ON dbo.policy_insured_dtl.gender = lookup_mstr_2.Lookup_seq INNER JOIN
'                      dbo.policy_trans ON dbo.policy_insured_dtl.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.lookup_mstr.Lookup_cd = N'SALUTATION') AND (lookup_mstr_1.Lookup_cd = N'MARITAL_STATUS') AND (lookup_mstr_2.Lookup_cd = N'GENDER') 
'                      AND (dbo.policy_trans.policy_no = N'1')

'mpz_Investments
'SELECT     dbo.fund_mstr.fund_desc, dbo.policy_fund_dtl.allocation
'FROM         dbo.policy_fund_dtl INNER JOIN
'                      dbo.fund_mstr ON dbo.policy_fund_dtl.fund_cd = dbo.fund_mstr.fund_cd INNER JOIN
'                      dbo.policy_trans ON dbo.policy_fund_dtl.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.policy_trans.policy_no = N'1')

'mpz_Owner_Info
'SELECT     dbo.policy_client_dtl.policy_client_last_name, dbo.policy_client_dtl.policy_client_first_name, dbo.policy_client_dtl.policy_client_middle_name, 
'                      dbo.policy_client_dtl.policy_client_alias, dbo.policy_client_dtl.birthday, dbo.policy_client_dtl.id_no, dbo.policy_client_dtl.occupation, 
'                      dbo.policy_client_dtl.policy_client_position, dbo.policy_client_dtl.nationality_cd, 
'                      dbo.lookup_mstr.Lookup_desc AS Salutation, lookup_mstr_1.Lookup_desc AS Marital_Status, lookup_mstr_2.Lookup_desc AS Gender
'FROM         dbo.lookup_mstr INNER JOIN
'                      dbo.policy_client_dtl ON dbo.lookup_mstr.Lookup_seq = dbo.policy_client_dtl.policy_client_salutation INNER JOIN
'                      dbo.lookup_mstr AS lookup_mstr_1 ON dbo.policy_client_dtl.marital_st = lookup_mstr_1.Lookup_seq INNER JOIN
'                      dbo.lookup_mstr AS lookup_mstr_2 ON dbo.policy_client_dtl.gender = lookup_mstr_2.Lookup_seq INNER JOIN
'                      dbo.policy_trans ON dbo.policy_client_dtl.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.policy_client_dtl.owner_tag = 1) AND (dbo.lookup_mstr.Lookup_cd = N'SALUTATION') AND (lookup_mstr_1.Lookup_cd = N'MARITAL_STATUS') AND 
'                      (lookup_mstr_2.Lookup_cd = N'GENDER') AND (dbo.policy_trans.policy_no = N'1')

'mpz_Prem_Payments
'SELECT     dbo.policy_premium_dtl.policy_due_dt, dbo.lookup_mstr.Lookup_desc AS Currency, dbo.policy_premium_dtl.contribution_amt
'FROM         dbo.policy_premium_dtl INNER JOIN
'                      dbo.lookup_mstr ON dbo.policy_premium_dtl.currency_cd = dbo.lookup_mstr.Lookup_seq INNER JOIN
'                      dbo.policy_trans ON dbo.policy_premium_dtl.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.lookup_mstr.Lookup_cd = N'CURRENCY') AND (dbo.policy_premium_dtl.pay_tag = 1) AND (dbo.policy_trans.policy_no = N'1')

'mpz_Section2
'SELECT     policy_approval_dt AS Approval_Date, establish_period, commence_age, insured_amt AS Sum_Insured, contribution_amt AS Min_Contr_Period
'FROM         dbo.policy_trans
'WHERE     (policy_no = '...')

'mpz_Section4
'SELECT     dbo.frequency_mstr.frequency_desc, dbo.lookup_mstr.Lookup_desc AS Policy_Curr, lookup_mstr_1.Lookup_desc AS Payment_Curr
'FROM         dbo.policy_trans INNER JOIN
'                      dbo.frequency_mstr ON dbo.policy_trans.frequency_cd = dbo.frequency_mstr.frequency_cd INNER JOIN
'                      dbo.lookup_mstr ON dbo.policy_trans.currency_cd = dbo.lookup_mstr.Lookup_seq INNER JOIN
'                      dbo.lookup_mstr AS lookup_mstr_1 ON dbo.policy_trans.payment_curr = lookup_mstr_1.Lookup_seq
'WHERE     (dbo.lookup_mstr.Lookup_cd = N'CURRENCY') AND (lookup_mstr_1.Lookup_cd = N'CURRENCY') AND (dbo.policy_trans.policy_no = N'1')

'mpz_Section5
'SELECT     dbo.lookup_mstr.Lookup_desc AS Policy_Status, dbo.policy_trans.process_st
'FROM         dbo.policy_trans INNER JOIN
'                      dbo.lookup_mstr ON dbo.policy_trans.policy_st = dbo.lookup_mstr.Lookup_seq
'WHERE     (dbo.lookup_mstr.Lookup_cd = N'POLICY_STATUS') AND (dbo.policy_trans.policy_no = N'1')

'mpz_Trustees
'SELECT     dbo.policy_trustees_dtl.policy_trustees_last_name, dbo.policy_trustees_dtl.policy_trustees_first_name, 
'                      dbo.policy_trustees_dtl.policy_trustees_middle_name, dbo.lookup_mstr.Lookup_desc AS Gender, lookup_mstr_1.Lookup_desc AS Ranking, 
'                      dbo.policy_trustees_dtl.birthday
'FROM         dbo.policy_trustees_dtl INNER JOIN
'                      dbo.lookup_mstr ON dbo.policy_trustees_dtl.gender = dbo.lookup_mstr.Lookup_seq INNER JOIN
'                      dbo.lookup_mstr AS lookup_mstr_1 ON dbo.policy_trustees_dtl.ranking_cd = lookup_mstr_1.Lookup_seq INNER JOIN
'                      dbo.policy_trans ON dbo.policy_trustees_dtl.policy_seq_cd = dbo.policy_trans.policy_seq_cd
'WHERE     (dbo.lookup_mstr.Lookup_cd = N'GENDER') AND (lookup_mstr_1.Lookup_cd = N'BENEFICIARY_RANK') AND (dbo.policy_trans.policy_no = N'1')